USE [Finances]
GO
/****** Object:  StoredProcedure [dbo].[accountsGetBalanceById]    Script Date: 09/14/2008 22:07:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Kenley Capps
-- Create date: 09/14/08
-- Description:	Retrieves a balance for an account
-- =============================================
CREATE PROCEDURE [dbo].[accountsGetBalanceById] 
	-- Add the parameters for the stored procedure here
	@account_id uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
		@account_id as account_id,
		ISNULL(TL.amount, 0.00) as ledger, -- Amount after both reconciled and non-reconciled
		ISNULL(TA.amount, 0.00) as available -- Amount after only reconciled (right now balance)

	FROM
		Transactions T
	LEFT JOIN
	(
		SELECT
			T.account_id as account_id,
			SUM(T.amount) as amount
		FROM
			Transactions T
		INNER JOIN
			Transactions_Status TS ON T.transactions_status_id = TS.transactions_status_id
		WHERE
			T.account_id = @account_id
		GROUP BY
			T.account_id
	) TL ON T.account_id = TL.account_id
	
	LEFT JOIN
	(
		SELECT
			T.account_id as account_id,
			SUM(T.amount) as amount
		FROM
			Transactions T
		INNER JOIN
			Transactions_Status TS on T.transactions_status_id = TS.transactions_status_id
		WHERE
			TS.modifies_balance = 1
		AND
			T.account_id = @account_id
		GROUP BY
			T.account_id
	) TA ON T.account_id = TA.account_id

	GROUP BY
		T.account_id, TL.amount, TA.amount
END
